package com.kevin.action.builder;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.kevin.action.BuildSql;
import com.kevin.enums.DBType;
import com.kevin.exceptions.CreaterException;
import com.kevin.exceptions.ExceptionFactory;
import com.kevin.executors.Executor;
import com.kevin.sql.DataType;
import com.kevin.sql.SqlFormat;
import com.kevin.table.MCellStruct;
import com.kevin.table.MDataCell;
import com.kevin.table.MDataColumn;
import com.kevin.table.MDataRow;
import com.kevin.table.MDataTable;

public abstract class BaseSqlBuildHandler {
	private static final Logger LOGGER = LoggerFactory.getLogger(BaseSqlBuildHandler.class);

	/// <summary>
	/// 首页查询
	/// </summary>
	protected final String PAGER = "select %s from %s where %s";
	/// <summary>
	/// 最后一页查询
	/// </summary>
	protected final String TOP2PAGER = "select top %s from (select %s from %s where %s) v %s";
	/// <summary>
	/// 前半段分页查询
	/// </summary>
	private final String TOP3PAGER = "select top %s %s from (select top %s * from (select top %s * from %s where %s) v %s) v %s";
	/// <summary>
	/// 后半段分页查询（即倒过来查询）
	/// </summary>
	private final String TOP4PAGER = "select %s from (select top %s * from (select top %s * from (select top %s * from %s where %s) v %s) v %s) v %s";
	/// <summary>
	/// MSSQL、Oracle的行号分页
	/// </summary>
	protected final String ROWNUMBERPAGER = "select * from(select row_number() over (%s) cyqrownum,%s from %s t %s)%s where cyqrownum between %s and %s";
	/// <summary>
	/// 临时表分页（不带自增加序列）
	/// </summary>
	private final String TEMPTABLEPAGER = "select top %s,cyqrownum=identity(int) into #tmp%s from %s where %s select top %s * from #tmp%s where cyqrownum between %s and %s %s drop table #tmp%s";
	/// <summary>
	/// 临时表分页（带自增加序列）
	/// </summary>
	private final String TEMPTABLEPAGERWITHIDENTITY = "select top %s cast(%s as int) cyqrowid,cyqrownum=identity(int) into #tmp%s from %s where %s select top %s %s from #tmp%s left join %s on %s=cyqrowid where cyqrownum between %s and %s %s drop table #tmp%s";

	/**
	 * 存储数据结构的行
	 */
	protected MDataRow struct;
	private DBType dbType;
	protected String tableName;

	public MDataRow getStruct() {
		return struct;
	}

	public BaseSqlBuildHandler(String tableName, Executor executor, DBType dbType) {
		this.tableName = tableName;
		try {
			MDataColumn mdcs = executor.getColumns(tableName);
			if (mdcs.size() == 0) {
				throw ExceptionFactory.Exception(new Exception(tableName + "is not exist!"));
			}
			this.struct = mdcs.toRow(tableName);
		} catch (SQLException e) {

		}
		this.struct.setTableName(this.tableName);
		this.dbType = dbType;
	}

	public String selectSql(String[] columns, Integer pageIndex, Integer pageSize, int total, String where) {
		if(StringUtils.isEmpty(where)) where = StringUtils.EMPTY;
		where = SqlUtils.removeOneEqOne(where);		
		String sql = buildSelectSql(pageIndex, pageSize, where, total, formatColumn(columns));
		return sql;
	}

	public String countSql(String sqlWhere) {
		String where = formatWhere(sqlWhere, struct.getColumns(), true);
		if (StringUtils.isNotEmpty(where)) {
			where = " where " + where;
			where = SqlUtils.removeOrderBy(where);
		}else{
			where = StringUtils.EMPTY;
		}

		return "select count(*) from " + tableName + where;
	}

	public ArrayList<BuildSql> insertSql(MDataTable table){
		ArrayList<BuildSql> sqlList = new ArrayList<>();
		BuildSql buildSql = null;
		for (MDataRow row : table.getRows()) {
			buildSql = new BuildSql();
			buildInsertSql(buildSql, row);
			//buildSql.appendSql(";");
			//buildSql.setAppendSqlEnable(false);
			sqlList.add(buildSql);
		}
		return sqlList;
	}

	
	public ArrayList<BuildSql> updateSql(MDataTable table,String where){
		ArrayList<BuildSql> sqlList = new ArrayList<>();
		BuildSql buildSql = null;
		for (MDataRow row : table.getRows()) {
			buildSql = new BuildSql();
			buildUpdateSql(buildSql, row,where);
			//buildSql.appendSql(";");
			//buildSql.setAppendSqlEnable(false);
			sqlList.add(buildSql);
		}
		return sqlList;
	}
	
	public BuildSql updateSql(MDataRow row,String where){
		BuildSql buildSql = new BuildSql();
		buildUpdateSql(buildSql, row,where);
		return buildSql;
	}
	
	public String deleteSql(String where) {
		if (where == null || where.equals(StringUtils.EMPTY)) {
			where = getPrimaryWhere();
		}
		return "delete from " + tableName + " where " + where;
	}

	public String getPrimaryWhere() {
		return getWhere(struct.getJointPrimaryCell());
	}
	
	/**
	 * 格式化where语句
	 * 
	 * @param where
	 * @param dco
	 * @param isFilterInjection
	 *            是否处理sql注入
	 * @return
	 */
	public String formatWhere(String where, MDataColumn dcol, boolean isFilterInjection) {
		if (StringUtils.isEmpty(where))
			return where;
		if (dcol == null || dcol.size() == 0) {
			throw new CreaterException("Column.Count=0");
		}
		where = SqlFormat.compatible(StringUtils.trim(where), dbType, isFilterInjection);
		where = formatWhereByColumn(where, dcol);
		String lowerWhere = where.toLowerCase().trim();
		if (lowerWhere.startsWith("order by")) {
			where = "1=1" + where;
		} else if (StringUtils.indexOfAny(lowerWhere, '=', '>', '<') == -1 && !lowerWhere.contains(" like ")
				&& !lowerWhere.contains(" between ") && !lowerWhere.contains(" in ") && !lowerWhere.contains(" in(")
				&& !lowerWhere.contains(" is ")) {
			// 检测是否带order by 'xxxx order by xxx desc'
			int index = where.replace("\n", " ").indexOf(" order by ");// 考虑可能换行的情况xxxx\r\norder
																		// by
																		// xxx
			String orderBy = StringUtils.EMPTY;
			if (index > -1) {
				orderBy = where.substring(index + 1, where.length() - index - 1);
				where = where.substring(0, index);
			}
			if (dcol.getJointPrimary().size() > 1 && where.contains(";")) {
				// 多个主键
				StringBuffer sb = new StringBuffer();
				String[] items = StringUtils.split(where, ',');
				MDataRow row = dcol.toRow("row");
				for (int i = 0; i < items.length; i++) {
					String item = items[i];
					if (StringUtils.isNotEmpty(item)) {
						String[] values = StringUtils.split(item, ';');
						for (int j = 0; j < row.getJointPrimaryCell().size(); j++) {
							if (j < values.length) {
								row.getJointPrimaryCell().get(j).setValue(values[j]);
							}
						}
						if (i != 0) {
							sb.append(" or ");
						}
						sb.append("(" + getWhere(row.getJointPrimaryCell()) + ")");
					}
				}
				where = sb.toString();
				if (items.length == 1) {
					where = StringUtils.strip(where, "()");
				}
				items = null;
			} else {
				MCellStruct ms = dcol.getFirstPrimary();

				String[] items = where.split(",");
				if (items.length == 1) {
					// 只处理单个值的情况
					int primaryGroupID = DataType.getGroup(ms.getSqlType());// 优先匹配主键
					switch (primaryGroupID) {
					case 1:
						long val = Long.parseLong(StringUtils.trim("\'"));
						ms = dcol.getFirstUnique();
						break;
					}

					String columnName = SqlFormat.keyword(ms.getColumnName(), dbType);
					where = getWhereEqual(DataType.getGroup(ms.getSqlType()), columnName, where);
				} else {
					ArrayList<String> lists = new ArrayList<String>(items.length);
					lists.addAll(Arrays.asList(items));
					where = getWhereIn(ms, lists);
				}
			}
			if (StringUtils.isNotEmpty(orderBy)) {
				where = where + " " + orderBy;
			}
		}

		return where;
	}

	private String formatColumn(String... columns) {
		if (columns == null || columns.length == 0) {
			return "*";
		}
		String v_Columns = StringUtils.EMPTY;
		String columnName = StringUtils.EMPTY;
		for (String column : columns) {
			columnName = column.trim();
			if (columnName.indexOf(' ') > -1 || columnName == "*") {
				v_Columns += columnName + ",";
			} else {
				int i = struct.getColumns().indexOf(columnName);// 兼容字段映射
				if (i > -1) {
					v_Columns += SqlFormat.keyword(struct.getColumns().get(i).getColumnName(), dbType) + ",";
				} else {
					LOGGER.warn(tableName + " no contains column " + columnName + "!");
				}
			}
		}
		if (v_Columns == StringUtils.EMPTY) {
			return "*";
		}
		return StringUtils.stripEnd(v_Columns, ",");
	}
	
	/**
	 * 根据元数据列组合where条件。
	 * 
	 * @param cells
	 * @return
	 */
	protected String getWhere(ArrayList<MDataCell> cells) {
		return getWhere(true, cells);
	}
	
	
	
	protected String getWhereEqual(int groupID, String columnName, String where) {
		if (StringUtils.isEmpty(where)) {
			return StringUtils.EMPTY;
		}
		if (groupID != 0) {
			where = StringUtils.strip(where, "\'");
		}
		if (groupID == 1) // int 类型的，主键不为bit型。
		{
			where = columnName + "=" + where;
		} else {
			where = columnName + "='" + where + "'";
		}
		return where;
	}
	
	
	abstract String buildSelectSql(int pageIndex, int pageSize, String where, int rowCount, String columns);
	abstract void buildInsertSql(BuildSql buildSql,MDataRow row);
	abstract void buildUpdateSql(BuildSql buildSql,MDataRow row,String where);
	abstract String Prefix();
	abstract String getWhere(boolean isAnd, ArrayList<MDataCell> cells);
	abstract String getWhereIn(MCellStruct ms, ArrayList<String> items);
	abstract String formatWhereByColumn(String where, MDataColumn dcol);
}
